use master
go
if object_id('sp_SQLBits_Scripter_GetObjectScript') is not null
  drop procedure sp_SQLBits_Scripter_GetObjectScript
go
/*
-----------------sp_SQLBits_Scripter_GetObjectScript-----------------------

    This procedure is called from management studio via a shortcut
    to pass a initate a request to service broker to generate a script
    for the object.
    This has to be run in master to avoid having to deoply it to all 
    the databases. 

----------------------------------------------------------------------------
(c) 2007 Simon Sabin : SQL Server MVP : http://sqlblogcasts.com/blogs/simons 

*/
create procedure sp_SQLBits_Scripter_GetObjectScript
  @objectName sysname  -- objectname to generate a script for
as
begin
  set nocount on

  declare @request xml  -- xml variable to hold the request
  declare @sql nvarchar(max)

  --generate the request in xml, allows us to package up the information
  set @sql = 'set @request = 
  (
  select *
    from (select db_name() DatabaseName -- this has to be found here as it is 
                                        -- lost once we call a procedure in another database
               , @objectName ObjectName
               , type        ObjectType
           from ' + db_name() + '..sysobjects
          where name = @objectName) ScriptRequest
    for xml auto)'

  exec sp_executesql @sql, N'@request xml output,@objectName sysname',@request output, @objectName

  if @request is null
    print 'Object not found'
  else
    --Call the main procedure in msdb. I didn't want to have service broker in master and 
    --you cant send from a service in another database.
    exec msdb.dbo.up_SQLBits_Scripter_GetObjectScript @request
end
go
use msdb
go
if object_id('up_SqlBits_Scripter_GetObjectScript') is not null
  drop procedure up_SqlBits_Scripter_GetObjectScript
go

/*
-----------------up_SQLBits_Scripter_GetObjectScript-----------------------

    This procedure is called from procedure in master and sends the request 
    to the service broker service.
    
----------------------------------------------------------------------------
(c) 2007 Simon Sabin : SQL Server MVP : http://sqlblogcasts.com/blogs/simons 
*/
create procedure up_SqlBits_Scripter_GetObjectScript
  @request xml  --request
as
   set nocount on

   --variable to hold the handle for the dialog
   declare @dialog uniqueidentifier

   --start a dialog to the Generator Service
   begin dialog @dialog 
    from service [SqlBits.Scripter.ResponseService]
      to service 'SqlBits.Scripter.GeneratorService'  --This case sensitive
     with encryption =off;
    
    --send the request for the script
    send on conversation @dialog (@request);

    --now we need to wait for a response
    declare @message_body nvarchar(max);
    declare @message_type_name nvarchar(max);

    --the receive statment allows us to receive a message from a queue
    --in conjunction with the wait statement we can wait until a message is on the queue
    waitfor (receive top (1)
                    @message_body= message_body
                   ,@message_type_name = message_type_name
                from [SqlBits.Scripter.ResponseQueue]
             where conversation_handle = @dialog) --We want to receive the response to 
                                                  --the request we sent, this will come 
                                                  --back on the same conversation
           , timeout 5000; --the timeout indicates hold long to wait

    --print the script we get back
    print @message_body;
go
if object_id('up_SqlBits_Scripter_ReceiveScriptRequest') is not null
    drop procedure up_SqlBits_Scripter_ReceiveScriptRequest
go
/*
-----------------up_SqlBits_Scripter_ReceiveScriptRequest------------------

    This procedure is called to receive the request from the service broker 
    queue.
    
----------------------------------------------------------------------------
(c) 2007 Simon Sabin : SQL Server MVP : http://sqlblogcasts.com/blogs/simons 
*/
create procedure up_SqlBits_Scripter_ReceiveScriptRequest
as
  begin
  set nocount on

  --local variables to hold the message and the conversation handle
  declare @message_body xml
  declare @conversation_handle uniqueidentifier

  --receive the request message from the queue we need the conversation 
  --handle to be able to send the script back to the requestor
  waitfor (receive top (1) @conversation_handle = conversation_handle
                          ,@message_body = message_body
              from [SqlBits.Scripter.GeneratorQueue]
          ),timeout 5000; -- only wait for 5 seconds

  if @@rowcount > 0
      --Extract the parameters from the request
      select @message_body.value('/ScriptRequest[1]/@DatabaseName', 'sysname')
            ,@message_body.value('/ScriptRequest[1]/@ObjectName','sysname')
            ,@conversation_handle
            

  end
go
if object_id('up_SqlBits_Scripter_ReturnScriptRequest') is not null
    drop procedure up_SqlBits_Scripter_ReturnScriptRequest
go
/*
-----------------up_SqlBits_Scripter_ReturnScriptRequest------------------

    This procedure is called to return the generated script back to the 
    requestor
    
----------------------------------------------------------------------------
(c) 2007 Simon Sabin : SQL Server MVP : http://sqlblogcasts.com/blogs/simons 
*/
create procedure up_SqlBits_Scripter_ReturnScriptRequest
    @script nvarchar(max)     --generated script
   ,@dialog uniqueidentifier  --dialog to return the script on
as
  set nocount on;

  --Send the script back to the requestor
  send on conversation @dialog (@script)
go
  
--sysjobs
select * from [SqlBits.Scripter.GeneratorQueue]

